package org.jsets.fastboot.database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Objects;
import javax.sql.DataSource;
import org.jsets.fastboot.util.IoUtils;
import org.jsets.fastboot.util.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.google.common.collect.Lists;

/**
 * 
 * JDBC相关工具
 * @author wj596
 *
 */
public class JdbcUtils {
	
	private static final Logger log = LoggerFactory.getLogger(JdbcUtils.class);
	
	private final static Integer CONNECT_RETRIE_TIMES = 3;
	
	
	/**
	 * 获取数据库连接
	 * @param type 数据库类型
	 * @param url 连接地址
	 * @param username 用户名
	 * @param password 密码
	 */
	public static Connection getConnection(DatabaseType type, String url, String username, String password) {
		String driverClassName = getDriverClassName(type);
		Connection conn = null;
		try {
			Class.forName(driverClassName);
			conn = DriverManager.getConnection(url, username, password);
			return conn;
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}
	}
	
    /**
     * 打开连接
     * <br>最多重试3次
     * @param dataSource 数据源
     */
    public static Connection openConnection(final DataSource dataSource) {
        int retries = 0;
        while (true) {
            try {
                return dataSource.getConnection();
            } catch (SQLException e) {
                if ("08S01".equals(e.getSQLState()) && e.getMessage().contains("This driver is not configured for integrated authentication")) {
                    throw new RuntimeException("无法获取数据库连接: " + e.getMessage(), e);
                } else if (e.getSQLState() == null && e.getMessage().contains("MSAL4J")) {
                    throw new RuntimeException("无法获取数据库连接,需要安装一些额外的驱动程序，以便进行交互身份验证"+ e.getMessage(), e);
                }

                if (++retries > CONNECT_RETRIE_TIMES) {
                    throw new RuntimeException("无法获取数据库连接:"+ e.getMessage(), e);
                }
                
                log.warn("获取数据库连接失败："+e.getMessage());
                log.info("1秒之后重试获取数据库连接");

                try {
                    Thread.sleep(1 * 1000);
                } catch (InterruptedException e1) {
                    throw new RuntimeException("获取数据库连接失败："+e.getMessage(), e);
                }
            }
        }
    }

    /**
     *  关闭连接
     */
    public static void closeConnection(final Connection connection) {
        if (connection == null) {
            return;
        }

        try {
            connection.close();
        } catch (Exception e) {
        	log.error("关闭数据库连接错误: " + e.getMessage(), e);
        }
    }
    
    /**
     * 关闭Statement
     */
    public static void closeStatement(final Statement statement) {
        if (statement == null) {
            return;
        }

        try {
            statement.close();
        } catch (SQLException e) {
        	log.error("关闭Statement错误: " + e.getMessage(), e);
        }
    }

    /**
     * 关闭ResultSet
     */
    public static void closeResultSet(final ResultSet resultSet) {
        if (resultSet == null) {
            return;
        }

        try {
            resultSet.close();
        } catch (SQLException e) {
            log.error("关闭ResultSet错误: " + e.getMessage(), e);
        }
    }
    
	/**
	 * 获取所有表名称
	 */
	public static List<String> getTableList(final Connection connection) {
		ResultSet resultSet = null;
		List<String> tableNames = Lists.newLinkedList();
		try {
			DatabaseMetaData dmd = connection.getMetaData();
			resultSet = dmd.getTables(connection.getCatalog(), "%", "%", new String[] { "TABLE" });
			while (resultSet.next()) {
				String tableName = resultSet.getString("TABLE_NAME");
				tableNames.add(StringUtils.toLowerCase(tableName));
			}
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			IoUtils.closeQuietly(resultSet);
			IoUtils.closeQuietly(connection);
		}
		return tableNames;
	}
    
    /**
     * 获取数据库元数据
     */
    public static DatabaseMetaData getDatabaseMetaData(final Connection connection) {
        DatabaseMetaData databaseMetaData;
        try {
            databaseMetaData = connection.getMetaData();
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
        if (databaseMetaData == null) {
            throw new RuntimeException("无法读取DatabaseMetaData");
        }
        return databaseMetaData;
    }
    
	/**
	 * 获取表元数据列表
	 * @param connection 数据库连接
	 */
	public static List<TableMetadata> getTableMetadataList(final Connection connection) {
		Objects.requireNonNull(connection);

		ResultSet pkSet = null;
		ResultSet tableSet = null;
		List<TableMetadata> tableMetadatas = Lists.newArrayList();
		try {
			DatabaseMetaData databaseMetadata = connection.getMetaData();
			tableSet = databaseMetadata.getTables(connection.getCatalog(), "%", "%", new String[] { "TABLE" });
			String catalog = connection.getCatalog();
			while (tableSet.next()) {
				String currentTableName = tableSet.getString("TABLE_NAME");//表名
				String tableCat = tableSet.getString("TABLE_CAT");//表的目录
				String tableSchem = tableSet.getString("TABLE_SCHEM");//模式的目录
				String tableType = tableSet.getString("TABLE_TYPE");//表的类型, 表\视图\系统表\全局表\别名\同义词等
				String remarks = tableSet.getString("REMARKS");//注释
				String typeSchem = tableSet.getString("TYPE_SCHEM");//表的架构
				String typeName = tableSet.getString("TYPE_NAME");//类型名称
				String selfReferencingColName = tableSet.getString("SELF_REFERENCING_COL_NAME");//表中指定列的名称
				String refGeneration = tableSet.getString("REF_GENERATION");//SYSTEM或USER或DERIVED
				TableMetadata tableMetadata = new TableMetadata();
				tableMetadata.setTableCat(tableCat);
				tableMetadata.setTableSchem(tableSchem);
				tableMetadata.setTableName(StringUtils.toLowerCase(currentTableName));
				tableMetadata.setTableType(tableType);
				tableMetadata.setRemarks(remarks);
				tableMetadata.setTypeSchem(typeSchem);
				tableMetadata.setTypeName(typeName);
				tableMetadata.setSelfReferencingColName(selfReferencingColName);
				tableMetadata.setRefGeneration(refGeneration);
					
				List<String> primaryKeys = Lists.newLinkedList();
				pkSet = databaseMetadata.getPrimaryKeys(null, null, currentTableName);
				while (pkSet.next()) {
					primaryKeys.add(StringUtils.toLowerCase(pkSet.getString("COLUMN_NAME")));
				}
				List<ColumnMetadata> columnMetadatas = getColumnMetaList(databaseMetadata, primaryKeys, catalog, currentTableName);
				tableMetadata.setColumnMetadatas(columnMetadatas);
				tableMetadatas.add(tableMetadata);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			IoUtils.closeQuietly(pkSet);
			IoUtils.closeQuietly(tableSet);
			IoUtils.closeQuietly(connection);
		}
		return tableMetadatas;
	}
    
	/**
	 * 获取表元数据
	 * @param connection 数据库连接
	 * @param tableName 表名称
	 */
	public static TableMetadata getTableMetadata(final Connection connection, final String tableName) {
		Objects.requireNonNull(connection);
		Objects.requireNonNull(tableName);
		
		TableMetadata tableMetadata = null;
		ResultSet pkSet = null;
		ResultSet tableSet = null;
		try {
			DatabaseMetaData databaseMetadata = connection.getMetaData();
			tableSet = databaseMetadata.getTables(connection.getCatalog(), "%", "%", new String[] { "TABLE" });
			String catalog = connection.getCatalog();
			while (tableSet.next()) {
				String currentTableName = tableSet.getString("TABLE_NAME");//表名
				if (tableName.equalsIgnoreCase(currentTableName)) {
					String tableCat = tableSet.getString("TABLE_CAT");//表的目录
					String tableSchem = tableSet.getString("TABLE_SCHEM");//模式的目录
					String tableType = tableSet.getString("TABLE_TYPE");//表的类型, 表\视图\系统表\全局表\别名\同义词等
					String remarks = tableSet.getString("REMARKS");//注释
					String typeSchem = tableSet.getString("TYPE_SCHEM");//表的架构
					String typeName = tableSet.getString("TYPE_NAME");//类型名称
					String selfReferencingColName = tableSet.getString("SELF_REFERENCING_COL_NAME");//表中指定列的名称
					String refGeneration = tableSet.getString("REF_GENERATION");//SYSTEM或USER或DERIVED
					tableMetadata = new TableMetadata();
					tableMetadata.setTableCat(tableCat);
					tableMetadata.setTableSchem(tableSchem);
					tableMetadata.setTableName(StringUtils.toLowerCase(currentTableName));
					tableMetadata.setTableType(tableType);
					tableMetadata.setRemarks(remarks);
					tableMetadata.setTypeSchem(typeSchem);
					tableMetadata.setTypeName(typeName);
					tableMetadata.setSelfReferencingColName(selfReferencingColName);
					tableMetadata.setRefGeneration(refGeneration);
					
					List<String> primaryKeys = Lists.newLinkedList();
					pkSet = databaseMetadata.getPrimaryKeys(null, null, currentTableName);
					// pkSet包含:
					//TABLE_CAT 	 	表的目录
					//TABLE_SCHEM 	 	模式目录
					//TABLE_NAME 	 	表名
					//COLUMN_NAME 	    列名
					//KEY_SEQ 	 	    序列号为主键
					//PK_NAME 	        主键名称
					while (pkSet.next()) {
						primaryKeys.add(StringUtils.toLowerCase(pkSet.getString("COLUMN_NAME")));
					}
					List<ColumnMetadata> columnMetadatas = getColumnMetaList(databaseMetadata, primaryKeys, catalog, currentTableName);
					tableMetadata.setColumnMetadatas(columnMetadatas);
					break;
				}
			}
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			IoUtils.closeQuietly(pkSet);
			IoUtils.closeQuietly(tableSet);
			IoUtils.closeQuietly(connection);
		}
		return tableMetadata;
	}

	/**
	 * @param databaseMetaData
	 * @param catalog
	 * @param tableName
	 * @return
	 */
	private static List<ColumnMetadata> getColumnMetaList(final DatabaseMetaData databaseMetadata, final List<String> primaryKeys, final String catalog, final String tableName) {
		ResultSet columnResultSet = null;
		try {
			List<ColumnMetadata> columnMetadatas = Lists.newLinkedList();
			columnResultSet = databaseMetadata.getColumns(catalog, "%", tableName, "%");
			while (columnResultSet.next()) {
				//String tableCat = columnResultSet.getString("TABLE_CAT") ;//目录名称。
				//String tableSchem = columnResultSet.getString("TABLE_SCHEM");//表架构名称。
				//String tableName = columnResultSet.getString("TABLE_NAME");//表名称。
				String columnName = columnResultSet.getString("COLUMN_NAME");//列名称。
				Integer dataType = columnResultSet.getInt("DATA_TYPE");//来自 java.sql.Types 的 SQL 数据类型。
				String typeName = columnResultSet.getString("TYPE_NAME");//数据类型的名称。
				Integer columnSize = columnResultSet.getInt("COLUMN_SIZE");//列的精度。
				Integer bufferLength = columnResultSet.getInt("BUFFER_LENGTH");//数据的传输大小。
				Integer decimalDigits = columnResultSet.getInt("DECIMAL_DIGITS");//列的小数位数。
				Integer numPrecRadix = columnResultSet.getInt("NUM_PREC_RADIX");//列的基数。
				Integer nullable = columnResultSet.getInt("NULLABLE");//指示列是否可以为 Null。 可以为下列值之一：columnNoNulls (0) columnNullable (1)
				String remarks = columnResultSet.getString("REMARKS");//与列关联的注释。注意：对于此列，SQL Server 始终会返回 null。
				String columnDef = columnResultSet.getString("COLUMN_DEF");//列的默认值。
				Integer sqlDataType = columnResultSet.getInt("SQL_DATA_TYPE");//SQL数据类型在描述符的 TYPE 字段中显示的值。 该列与 DATA_TYPE 列相同，datetime 和 SQL-92 interval 数据类型除外。 该列始终返回值。
				Integer sqlDatetimeSub = columnResultSet.getInt("SQL_DATETIME_SUB");//datetime 及 SQL-92 interval 数据类型的子类型代码。 对于其他数据类型，该列返回 NULL。
				Integer charOctetLength = columnResultSet.getInt("CHAR_OCTET_LENGTH");//列中的最大字节数。
				Integer ordinalPosition = columnResultSet.getInt("ORDINAL_POSITION");//列在表中的索引。
				String isNullable = columnResultSet.getString("IS_NULLABLE");//指示列是否允许 Null 值。

				ColumnMetadata columnMetadata = new ColumnMetadata();
				columnMetadata.setColumnName(StringUtils.toLowerCase(columnName));
				columnMetadata.setDataType(dataType);
				columnMetadata.setTypeName(StringUtils.toLowerCase(typeName));
				columnMetadata.setColumnSize(columnSize);
				columnMetadata.setBufferLength(bufferLength);
				columnMetadata.setDecimalDigits(decimalDigits);
				columnMetadata.setNumPrecRadix(numPrecRadix);
				columnMetadata.setNullable(nullable);
				columnMetadata.setRemarks(remarks);
				columnMetadata.setColumnDef(columnDef);
				columnMetadata.setSqlDataType(sqlDataType);
				columnMetadata.setSqlDatetimeSub(sqlDatetimeSub);
				columnMetadata.setCharOctetLength(charOctetLength);
				columnMetadata.setOrdinalPosition(ordinalPosition);
				columnMetadata.setIsNullable(isNullable);
				columnMetadata.setPrimaryKey(primaryKeys.contains(columnMetadata.getColumnName()));
				columnMetadatas.add(columnMetadata);
			}
			return columnMetadatas;
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			IoUtils.closeQuietly(columnResultSet);
		}
	}
	
	
    /**
     * 获取数据库名称
     */
    public static String getDatabaseProductName(final DatabaseMetaData databaseMetaData) {
        try {
            String databaseProductName = databaseMetaData.getDatabaseProductName();
            if (databaseProductName == null) {
                throw new RuntimeException("DatabaseProductName为空");
            }

            int databaseMajorVersion = databaseMetaData.getDatabaseMajorVersion();
            int databaseMinorVersion = databaseMetaData.getDatabaseMinorVersion();

            return databaseProductName + " " + databaseMajorVersion + "." + databaseMinorVersion;
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }
    
    /**
     * 获取数据库名称
     */
	public static String getDatabaseProductName(final DataSource dataSource) {
		Connection connection = null;
		try {
			connection = openConnection(dataSource);
			DatabaseMetaData databaseMetaData = getDatabaseMetaData(connection);
			String databaseProductName = getDatabaseProductName(databaseMetaData);
			return databaseProductName;
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			closeConnection(connection);
		}
	}
	
    /**
     * 数据库是否MySQL
     */
	public static boolean isMySQL(final DataSource dataSource) {
		Connection connection = null;
		try {
			connection = openConnection(dataSource);
			DatabaseMetaData databaseMetaData = getDatabaseMetaData(connection);
            String databaseProductName = databaseMetaData.getDatabaseProductName();
			return "MYSQL".equals(databaseProductName.toUpperCase());
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			closeConnection(connection);
		}
	}
	
    /**
     * 获取驱动程序名称
     */
	public static String getDriverClassName(final DatabaseType type) {
		switch (type) {
		case MySQL:
			return "com.mysql.cj.jdbc.Driver";
		case Oracle:
			return "oracle.jdbc.driver.OracleDriver";
		default: 
			throw new RuntimeException("不支持该类型数据库");
		}
	}

}